package hedron.db;

import static hedron.Application.log;
import hedron.Application;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.StringTokenizer;
import java.util.logging.Level;
import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.tools.Server;

public class DBManager {
	public static final DBManager instance;
	public static final boolean isServerMode;
	public static String SQLError = null;

	private static final String localhost = "localhost";
	private static final String user = "administrator";
	private static final String pwds = "2975bbc439c2127f7774fcbb2e100651";// md5(md5(hedron))
	private static final String url;

	static Server H2Server, H2WebConsole;
	static JdbcConnectionPool connectionPool;
	private final static SimpleDateFormat simpleDateFormater;

	static {
		String serverHost = Application.getProperty(Application.CORE_DB_HOST, localhost);
		isServerMode = localhost.equalsIgnoreCase(serverHost);
		if (isServerMode) {
			url = "jdbc:h2:./data/HEDRON";
		} else {
			url = "jdbc:h2:tcp://" + serverHost + ":9092/./data/HEDRON";
		}
		instance = new DBManager();

		simpleDateFormater = new SimpleDateFormat();
		simpleDateFormater.applyPattern("yyyy-MM-dd hh:mm:ss");
	}

	protected DBManager() {
		try {
			Class.forName("org.h2.Driver");
			if (isServerMode) {// local/server mode
				log.info("Starting in Server mode.....");
				try {
					// tcp://localhost:9092/
					H2Server = Server.createTcpServer(new String[] { "-tcpAllowOthers" }).start();
					log.info("H2 Server: " + H2Server.getURL());
					// http://localhost:8082/
					H2WebConsole = Server.createWebServer(new String[] {}).start();
					log.info("H2 Web Console: " + H2WebConsole.getURL());

					Connection conn = DriverManager.getConnection(url, user, pwds);
					if (conn.isClosed()) {
						log.log(Level.OFF, "Cannot connect to H2 local server !");
						Runtime.getRuntime().exit(1);
						return;
					} else {
						conn.close();
					}
				} catch (SQLException e) {
					log.log(Level.OFF, "Cannot create server!", (Throwable) e);
					Runtime.getRuntime().exit(1);
					return;
				}
			} else { // client mode - try to connect to server
				log.info("Starting in Client mode.....");
				try {
					Connection conn = DriverManager.getConnection(url, user, pwds);
					if (conn.isClosed()) {

					} else {
						conn.close();
					}
				} catch (SQLException e) {
					log.log(Level.OFF, "Cannot connect to server!", e);
					Runtime.getRuntime().exit(1);
					return;
				}
			}

			// initialize connection pool
			org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
			dataSource.setURL(url);
			dataSource.setUser(user);
			dataSource.setPassword(pwds);
			connectionPool = JdbcConnectionPool.create(dataSource);

			if (isServerMode) {// create DB schema if necessary
				Connection connection = getConnection();
				DBSchema.createDBSchema(this);
				try {
					connection.close();
				} catch (SQLException e) {
					log.log(Level.WARNING, "createDBSchema connection close problem:", e);
				}
			}

		} catch (ClassNotFoundException e) {
			log.log(Level.OFF, "Cannot find org.h2.Driver!", e);
			Runtime.getRuntime().exit(1);
		}
	}

	public void dispose() {
		if (connectionPool != null) {
			try {
				connectionPool.dispose();
			} catch (SQLException e) {
				log.log(Level.WARNING, "Connection Pool:", e);
			}
		}
		if (H2WebConsole != null) {
			log.info("H2 Web Console: closing ...");
			H2WebConsole.stop();
			H2WebConsole.shutdown();
			log.info("H2 Web Console: closed");
			H2WebConsole = null;
		}
		if (H2Server != null) {
			log.info("H2 Server: closing ...");
			H2Server.stop();
			H2Server.shutdown();
			log.info("H2 Server: closed");
			H2Server = null;
		}
	}

	public final Connection getConnection() {
		Connection conn = null;
		if (connectionPool != null) {
			try {
				conn = connectionPool.getConnection();
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				log.log(Level.WARNING, "Cannot pool a  DB Connection :", e);
			}
		}
		return conn;
	}

	public final void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				log.log(Level.WARNING, "Cannot close  DB Connection :", e);
			}
		}
	}

	public final Connection getTransaction() {
		Connection conn = null;
		if (connectionPool != null) {
			try {
				conn = connectionPool.getConnection();
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				log.log(Level.WARNING, "Cannot pool a  DB Connection :", e);
			}
		}
		return conn;
	}

	public final void commitTransaction(Connection conn) {
		if (conn != null) {
			try {
				conn.commit();
			} catch (SQLException e) {
				log.log(Level.WARNING, "Cannot commit DB Connection :", e);
				try {
					conn.rollback();
				} catch (SQLException e1) {
					log.log(Level.WARNING, "Cannot rollback DB Connection :", e1);
				}
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					log.log(Level.WARNING, "Cannot close DB Connection :", e);
				}
			}
		}
	}

	public final void rollbackTransaction(Connection conn) {
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.log(Level.WARNING, "Cannot rollback DB Connection :", e1);
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					log.log(Level.WARNING, "Cannot close DB Connection :", e);
				}
			}
		}
	}

	private final String preparedSQL(String sql, Object[] params) {
		int currentParam = 0;
		StringBuilder preparedSql = new StringBuilder();
		for (int i = 0; i < sql.length(); i++) {
			char c = sql.charAt(i);
			if (c == '?' && currentParam < params.length) {
				Object param = params[currentParam++];
				if (param instanceof String) {
					preparedSql.append('\'');
					preparedSql.append(escape(param.toString()));
					preparedSql.append('\'');
				} else if (param instanceof Boolean) {
					preparedSql.append(param.toString());
				} else if (param instanceof Number) {
					preparedSql.append(param.toString());
				} else if (param instanceof Date) {
					preparedSql.append('\'');
					preparedSql.append(simpleDateFormater.format((Date) param));
					preparedSql.append('\'');
				} else {
					throw new IllegalStateException("unsupported parameter");
				}
			} else {
				preparedSql.append(c);
			}
		}
		return preparedSql.toString();
	}

	public ResultSet query(Connection conn, String sql, Object... params) {
		ResultSet resultSet = null;
		Statement statement;
		try {
			// prepare statement since h2 does not support it
			if (sql != null && params != null && params.length > 0) {
				sql = preparedSQL(sql, params);
			}
			statement = conn.createStatement();
			resultSet = statement.executeQuery(sql);

		} catch (SQLException e) {
			SQLError = e.getMessage();
			Application.setStatusMessage(2, SQLError);
			log.log(Level.WARNING, "ddlQuery problem :", (Throwable) e);
			return null;
		}
		SQLError = null;
		return resultSet;
	}

	public void close(ResultSet rs) {
		try {
			rs.close();
		} catch (Exception e) {
			// NPE or SQL - nop
		}
	}

	public boolean ddlQuery(Connection conn, String sql, Object... params) {
		Statement statement;
		SQLError = null;
		try {
			// prepare statement since h2 does not support it
			if (sql != null && params != null && params.length > 0) {
				sql = preparedSQL(sql, params);
			}
			statement = conn.createStatement();
			statement.executeUpdate(sql);

		} catch (SQLException e) {
			SQLError = e.getMessage();
			Application.setStatusMessage(2, SQLError);
			log.log(Level.SEVERE, "ddlQuery problem :", e);
			return false;
		}
		return true;
	}

	public boolean first(ResultSet rs) {
		if (rs != null) {
			try {
				return rs.first();
			} catch (SQLException e) {
				log.log(Level.WARNING, "Cannot position at first element :", e);
			}
		}
		return false;
	}

	public boolean next(ResultSet rs) {
		if (rs != null) {
			try {
				return rs.next();
			} catch (SQLException e) {
				log.log(Level.WARNING, "Cannot position at first element :", e);
			}
		}
		return false;
	}

	public final long getLong(ResultSet rs, int col) {
		return getLong(rs, col, 0);
	}

	public final long getLong(ResultSet rs, int col, long defaultValue) {
		long result = defaultValue;
		if (rs != null) {
			try {
				result = rs.getLong(col);
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final long getLong(ResultSet rs, String colName) {
		return getLong(rs, colName, 0);
	}

	public final long getLong(ResultSet rs, String colName, long defaultValue) {
		long result = defaultValue;
		if (rs != null) {
			try {
				result = rs.getLong(colName);
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final double getDouble(ResultSet rs, String colName) {
		return getDouble(rs, colName, 0);
	}

	public final double getDouble(ResultSet rs, String colName, double defaultValue) {
		double result = defaultValue;
		if (rs != null) {
			try {
				result = rs.getDouble(colName);
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final String getString(ResultSet rs, int col) {
		return getString(rs, col, "");
	}

	public final String getString(ResultSet rs, int col, String defaultValue) {
		String result = defaultValue;
		if (rs != null) {
			try {
				result = rs.getString(col);
				if (result == null) {
					result = defaultValue;
				}
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final String getString(ResultSet rs, String colName) {
		return getString(rs, colName, "");
	}

	public final String getString(ResultSet rs, String colName, String defaultValue) {
		String result = defaultValue;
		if (rs != null) {
			try {
				result = rs.getString(colName);
				if (result == null) {
					result = defaultValue;
				}
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final Date getDate(ResultSet rs, int col) {
		Date result = null;
		if (rs != null) {
			try {
				result = rs.getDate(col);
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final Date getDate(ResultSet rs, String colName) {
		return getDate(rs, colName, null);
	}

	public final Date getDate(ResultSet rs, String colName, Date defaultDate) {
		Date result = defaultDate;
		if (rs != null) {
			try {
				result = rs.getDate(colName);
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	public final boolean getBoolean(ResultSet rs, String colName) {
		boolean result = false;
		if (rs != null) {
			try {
				result = rs.getBoolean(colName);
			} catch (SQLException e) {// not interested we have default value
			}
		}
		return result;
	}

	static String escape(String str) {
		if (str == null || str.length() <= 0) {
			return "";
		}
		StringBuilder result = new StringBuilder();
		for (int i = 0; i < str.length(); i++) {
			char c = str.charAt(i);
			if (c == '\'') {
				result.append('\'');
			} else if (c == '\\') {
				result.append('\\');
			}
			result.append(c);
		}
		return result.toString();
	}

	static void escape(StringBuilder buffer, String str) {
		if (str == null || str.length() <= 0) {
			return;
		}
		for (int i = 0; i < str.length(); i++) {
			char c = str.charAt(i);
			if (c == '\'') {
				buffer.append('\'');
			} else if (c == '\\') {
				buffer.append('\\');
			}
			buffer.append(c);
		}
	}

	String prepareSearchCondition(String field, String filter) {
		if (field == null || field.length() <= 0 || filter == null || filter.length() < 0) {
			return "";
		}
		StringBuilder result = new StringBuilder(filter.length() * 2);
		StringTokenizer token = new StringTokenizer(filter.trim(), " \r\t\t,+%");
		result.append('(');
		while (token.hasMoreTokens()) {
			if (result.length() > 1) {
				result.append(" OR ");
			}
			result.append(field);
			result.append(" like '%");
			escape(result, token.nextToken());
			result.append("%' ");
		}
		result.append(')');

		return result.toString();
	}
}
